package com.example.demo.utils;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.TemplateExportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity;
import cn.afterturn.easypoi.excel.entity.result.ExcelImportResult;
import cn.afterturn.easypoi.handler.inter.IExcelExportServer;
import cn.afterturn.easypoi.handler.inter.IWriter;
import cn.hutool.poi.excel.ExcelReader;
import com.example.demo.model.page.Page;
import com.monitorjbl.xlsx.StreamingReader;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.formula.functions.T;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.apache.tomcat.util.http.fileupload.IOUtils;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.util.*;

/**
 * @author
 */
@Slf4j
public class ExcelUtil {


    /**
     * 允许导出的最大条数
     */
    private static final Integer EXPORT_EXCEL_MAX_NUM = 10000;
    private static volatile ExcelUtil instance = null;

    private ExcelUtil() {
    }

    public static ExcelUtil getInstance() {
        if (null == instance) {
            synchronized (ExcelUtil.class) {
                if (null == instance) {
                    instance = new ExcelUtil();
                }
            }
        }
        return instance;
    }

    private static LinkedHashMap<String, String> getFieldNameAndFieldDesc(Class<?> clazz) {
        LinkedHashMap<String, String> fieldsMap = new LinkedHashMap<>();
        Field[] fields = clazz.getDeclaredFields();
        if (fields.length > 0) {
            Arrays.stream(fields).filter(field -> !"serialVersionUID".equals(field.getName()) && !field.getName().startsWith("this$")).forEach(field -> {
                Excel property = field.getAnnotation(Excel.class);
                if (property != null) {
                    fieldsMap.put(field.getName(), property.name());
                }
            });
        }
        return fieldsMap;
    }

    private static CellStyle cellStyle(Workbook workbook, int bg, boolean wrapped) {
        CellStyle cellStyle = workbook.createCellStyle();
        //设置背景颜色
        bg = Math.max(bg, 1);
        cellStyle.setFillForegroundColor((short) bg);
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        //边框
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderTop(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        //上下居中
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        //自动换行
        if (wrapped) {
            cellStyle.setWrapText(true);
        }
        return cellStyle;
    }

    private static Font font(Workbook workbook, String name, int height, boolean bold) {
        height = Math.max(height, 10);
        name = StringUtils.isBlank(name) ? "宋体" : name;
        Font font = workbook.createFont();
        font.setCharSet(Font.DEFAULT_CHARSET);
        font.setFontName(name);
        //font.setFontHeight((short) height);
        font.setFontHeightInPoints((short) height);
        if (bold) {
            font.setBold(true);
        }
        return font;
    }

    /**
     * 自动调整列宽
     *
     * @param sheet
     * @param columnNumber
     */
    private void autoSizeColumns(Sheet sheet, int columnNumber) {
        sheet.setColumnWidth(0, 1600);
        for (int i = 1; i < columnNumber; i++) {
            int orgWidth = sheet.getColumnWidth(i);
            sheet.autoSizeColumn(i, true);
            // 当newWidth的长度大于65000会导致报错
            int newWidth = sheet.getColumnWidth(i) + 100;
            if (newWidth > orgWidth) {
                sheet.setColumnWidth(i, newWidth);
            } else {
                sheet.setColumnWidth(i, orgWidth);
            }
        }
    }

    public static void bigDataExport(String exportFile, List<?> dataList, Class<?> pojoClass) {
        bigDataExport(exportFile, null, "Sheet1", dataList, pojoClass);
    }

    public static void bigDataExport(String exportFile, String title, List<?> dataList, Class<?> pojoClass) {
        bigDataExport(exportFile, title, "Sheet1", dataList, pojoClass);
    }

    public static void bigDataExport(String exportFile, String title, String sheetName, List<?> dataList, Class<?> pojoClass) {
        IWriter<Workbook> writer = null;
        Workbook workbook = null;
        FileOutputStream fos = null;
        try {
            ExportParams exportParams = new ExportParams(title, sheetName, ExcelType.XSSF);
            exportParams.setAutoSize(true);
            exportParams.setStyle(ExcelStyleUtil.class);
            //exportParams.setHeaderColor(HSSFColor.HSSFColorPredefined.GREY_25_PERCENT.getIndex());
            //exportParams.setHeaderHeight(13);
            writer = ExcelExportUtil.exportBigExcel(exportParams, pojoClass);
            writer.write(dataList);
            workbook = writer.get();
            File file = new File(exportFile);
            fos = new FileOutputStream(file);
            workbook.write(fos);
        } catch (IOException e) {
            log.error(e.getMessage(), e);
        } finally {
            try {
                if (null != writer) {
                    writer.close();
                }
                if (null != workbook) {
                    workbook.close();
                }
                if (null != fos) {
                    fos.close();
                }
            } catch (Exception e) {
                log.error(e.getMessage(), e);
            }
        }
    }

    public static void bigDataExport(String exportFile, List<?> dataList, List<ExcelExportEntity> excelParams) {
        bigDataExport(exportFile, null, "Sheet1", dataList, excelParams);
    }

    public static void bigDataExport(String exportFile, String title, List<?> dataList, List<ExcelExportEntity> excelParams) {
        bigDataExport(exportFile, title, "Sheet1", dataList, excelParams);
    }

    public static void bigDataExport(String exportFile, String title, String sheetName, List<?> dataList, List<ExcelExportEntity> excelParams) {
        IWriter<Workbook> writer = null;
        Workbook workbook = null;
        FileOutputStream fos = null;
        try {
            ExportParams exportParams = new ExportParams(title, sheetName, ExcelType.XSSF);
            exportParams.setAutoSize(Boolean.TRUE);
            exportParams.setStyle(ExcelStyleUtil.class);
            writer = ExcelExportUtil.exportBigExcel(exportParams, excelParams);
            writer.write(dataList);
            workbook = writer.get();
            File file = new File(exportFile);
            fos = new FileOutputStream(file);
            workbook.write(fos);
        } catch (IOException e) {
            log.error(e.getMessage(), e);
        } finally {
            try {
                if (null != writer) {
                    writer.close();
                }
                if (null != workbook) {
                    workbook.close();
                }
                if (null != fos) {
                    fos.close();
                }
            } catch (Exception e) {
                log.error(e.getMessage(), e);
            }
        }
    }

    public static void bigDataExport(String exportFile, Class<?> pojoClass, IExcelExportServer exportServer, Object queryParams) {
        bigDataExport(exportFile, null, "Sheet1", pojoClass, exportServer, queryParams);
    }

    public static void bigDataExport(String exportFile, String title, Class<?> pojoClass, IExcelExportServer exportServer, Object queryParams) {
        bigDataExport(exportFile, title, "Sheet1", pojoClass, exportServer, queryParams);
    }

    public static void bigDataExport(String exportFile, String title, String sheetName, Class<?> pojoClass, IExcelExportServer exportServer, Object queryParams) {
        Workbook workbook = null;
        FileOutputStream fos = null;
        try {
            ExportParams exportParams = new ExportParams(title, sheetName, ExcelType.XSSF);
            exportParams.setAutoSize(true);
            exportParams.setStyle(ExcelStyleUtil.class);
            //exportParams.setHeaderColor(HSSFColor.HSSFColorPredefined.GREY_25_PERCENT.getIndex());
            //exportParams.setHeaderHeight(13);
            workbook = ExcelExportUtil.exportBigExcel(exportParams, pojoClass, exportServer, queryParams);
            File file = new File(exportFile);
            fos = new FileOutputStream(file);
            workbook.write(fos);
        } catch (IOException e) {
            log.error(e.getMessage(), e);
        } finally {
            try {
                if (null != workbook) {
                    workbook.close();
                }
                if (null != fos) {
                    fos.close();
                }
            } catch (Exception e) {
                log.error(e.getMessage(), e);
            }
        }
    }

    public static void bigDataExport(String exportFile, List<ExcelExportEntity> excelParams, IExcelExportServer exportServer, Object queryParams) {
        bigDataExport(exportFile, null, "Sheet1", excelParams, exportServer, queryParams);
    }

    public static void bigDataExport(String exportFile, String title, List<ExcelExportEntity> excelParams, IExcelExportServer exportServer, Object queryParams) {
        bigDataExport(exportFile, title, "Sheet1", excelParams, exportServer, queryParams);
    }

    public static void bigDataExport(String exportFile, String title, String sheetName, List<ExcelExportEntity> excelParams, IExcelExportServer exportServer, Object queryParams) {
        Workbook workbook = null;
        FileOutputStream fos = null;
        try {
            ExportParams exportParams = new ExportParams(title, sheetName, ExcelType.XSSF);
            exportParams.setAutoSize(true);
            exportParams.setStyle(ExcelStyleUtil.class);
            //exportParams.setHeaderColor(HSSFColor.HSSFColorPredefined.GREY_25_PERCENT.getIndex());
            //exportParams.setHeaderHeight(13);
            workbook = ExcelExportUtil.exportBigExcel(exportParams, excelParams, exportServer, queryParams);
            File file = new File(exportFile);
            fos = new FileOutputStream(file);
            workbook.write(fos);
        } catch (IOException e) {
            log.error(e.getMessage(), e);
        } finally {
            try {
                if (null != workbook) {
                    workbook.close();
                }
                if (null != fos) {
                    fos.close();
                }
            } catch (Exception e) {
                log.error(e.getMessage(), e);
            }
        }
    }

    /**
     * excel 导出 (本地)
     *
     * @param list      数据列表
     * @param excelType HSSF, XSSF
     */
    public <T> void exportExcel(File file, List<T> list, Class<?> clazz, ExcelType excelType) throws IOException {
        FileOutputStream fos = null;
        Workbook workbook = null;
        try {
            fos = new FileOutputStream(file);
            String fileName = file.getName();
            if (StringUtils.isNotBlank(fileName)) {
                if (fileName.endsWith(".xls")) {
                    fileName = fileName.substring(0, fileName.indexOf(".xls"));
                }
                if (fileName.endsWith(".xlsl")) {
                    fileName = fileName.substring(0, fileName.indexOf(".xlsl"));
                }
            }
            ExportParams exportParams = new ExportParams(fileName, fileName, excelType);
            exportParams.setAutoSize(Boolean.TRUE);
            exportParams.setStyle(ExcelStyleUtil.class);
            //exportParams.setHeaderColor(HSSFColor.HSSFColorPredefined.SKY_BLUE.getIndex());
            workbook = ExcelExportUtil.exportExcel(exportParams, clazz, list);
            Sheet sheet = workbook.createSheet();
            this.autoSizeColumns(sheet, 60);
            workbook.write(fos);
            workbook.close();
        } catch (Exception e) {
            log.error(">>>>>> excel导出数据列表异常！", e);
        } finally {
            if (workbook != null) {
                workbook.close();
            }
            if (fos != null) {
                fos.close();
            }
        }
    }

    /**
     * excel 导出
     *
     * @param list     数据列表
     * @param fileName 导出时的excel名称
     * @param response
     */
    public void exportExcel(List<Map<String, Object>> list, String fileName, ExcelType excelType, HttpServletResponse response) throws IOException {
        this.defaultExport(list, fileName, excelType, response);
    }

    /**
     * 默认的 excel 导出
     *
     * @param list     数据列表
     * @param fileName 导出时的excel名称
     * @param response
     */
    private void defaultExport(List<Map<String, Object>> list, String fileName, ExcelType excelType, HttpServletResponse response) throws IOException {
        Workbook workbook = ExcelExportUtil.exportExcel(list, excelType);
        this.downLoadExcel(fileName, response, workbook);
    }

    public static void exportExcel(String exportFile, LinkedHashMap<String, String> tableHeaders, Collection<?> dataList) {
        exportExcel(exportFile, null, 1, 13, 11, tableHeaders, dataList);
    }

    public static void exportExcel(String exportFile, String title, int headBg, int headFontHeight, int bodyFontHeight, LinkedHashMap<String, String> tableHeaders, Collection<?> dataList) {
        LinkedList<ExcelExportEntity> entityList = new LinkedList<>();
        if (tableHeaders != null && tableHeaders.size() > 0) {
            tableHeaders.forEach((k, v) -> {
                if (v != null) {
                    entityList.add(new ExcelExportEntity(v, k));
                }
            });
        }
        List<Map<String, Object>> dataMapList = new ArrayList<>();
        if (CollectionUtils.isNotEmpty(dataList)) {
            dataList.forEach(o -> dataMapList.add(JacksonUtil.jsonToMap(JacksonUtil.beanToJson(o), Object.class)));
        }
        exportExcel(exportFile, title, "Sheet1", headBg, headFontHeight, bodyFontHeight, entityList, dataMapList);
    }

    public static void exportExcel(String exportFile, Collection<?> dataList, Class<?> clazz) {
        exportExcel(exportFile, null, 1, 13, 11, dataList, clazz);
    }

    public static void exportExcel(String exportFile, String title, int headBg, int headFontHeight, int bodyFontHeight, Collection<?> dataList, Class<?> clazz) {
        LinkedList<ExcelExportEntity> entityList = new LinkedList<>();
        if (clazz != null) {
            LinkedHashMap<String, String> tableHeaders = getFieldNameAndFieldDesc(clazz);
            tableHeaders.forEach((k, v) -> entityList.add(new ExcelExportEntity(v, k)));
        }
        List<Map<String, Object>> dataMapList = new ArrayList<>();
        if (CollectionUtils.isNotEmpty(dataList)) {
            dataList.forEach(o -> dataMapList.add(JacksonUtil.jsonToMap(JacksonUtil.beanToJson(o), Object.class)));
        }
        exportExcel(exportFile, title, "Sheet1", headBg, headFontHeight, bodyFontHeight, entityList, dataMapList);
    }

    public static void exportExcel(List<Map<String, Object>> dataMapList, LinkedList<ExcelExportEntity> entityList, String exportFile, String title) {
        exportExcel(exportFile, title, "Sheet1", 1, 13, 11, entityList, dataMapList);
    }

    public static void exportExcel(String exportFile, String title, String sheetName, int headBg, int headFontHeight, int bodyFontHeight, LinkedList<ExcelExportEntity> entityList, List<Map<String, Object>> dataMapList) {
        XSSFWorkbook workbook = new XSSFWorkbook();
        FileOutputStream fos = null;
        try {
            if (CollectionUtils.isNotEmpty(dataMapList)) {
                XSSFSheet sheet = workbook.createSheet(StringUtils.isBlank(sheetName) ? "Sheet1" : sheetName);
                Object[] headArr = new String[(entityList == null || entityList.size() < 1) ? dataMapList.get(0).keySet().size() : entityList.size()];
                LinkedList<String> headList = new LinkedList<>();
                if (entityList == null) {
                    headList.addAll(dataMapList.get(0).keySet());
                    for (int i = 0; i < headList.size(); i++) {
                        headArr = dataMapList.get(0).keySet().toArray();
                        sheet.autoSizeColumn(i);
                    }
                } else {
                    for (int i = 0; i < entityList.size(); i++) {
                        headArr[i] = entityList.get(i).getName();
                        headList.add((String) entityList.get(i).getKey());
                        sheet.autoSizeColumn(i);
                    }
                }
                XSSFRow row = sheet.createRow(0);
                row.setHeightInPoints(20);
                headBg = headBg < 1 ? 22 : headBg;
                headFontHeight = headFontHeight < 8 ? 11 : headFontHeight;
                XSSFCellStyle headStyle = (XSSFCellStyle) cellStyle(workbook, headBg, false);
                XSSFFont titleFont = (XSSFFont) font(workbook, "宋体", headFontHeight, true);
                XSSFCell cell;
                XSSFRichTextString text;
                for (int i = 0; i < headArr.length; i++) {
                    if (headArr[i] != null) {
                        cell = row.createCell(i);
                        text = new XSSFRichTextString(String.valueOf(headArr[i]));
                        text.applyFont(titleFont);
                        cell.setCellValue(text);
                        cell.setCellStyle(headStyle);
                    }
                }
                bodyFontHeight = bodyFontHeight < 8 ? 11 : bodyFontHeight;
                XSSFCellStyle dataStyle = (XSSFCellStyle) cellStyle(workbook, 1, true);
                XSSFFont dataFont = (XSSFFont) font(workbook, "宋体", bodyFontHeight, false);
                XSSFRow rows;
                for (int i = 0; i < dataMapList.size(); i++) {
                    rows = sheet.createRow(i + 1);
                    for (int j = 0; j < headList.size(); j++) {
                        String value;
                        if (dataMapList.get(i).get(headList.get(j)) != null) {
                            value = dataMapList.get(i).get(headList.get(j)).toString();
                        } else {
                            value = "";
                        }
                        text = new XSSFRichTextString(value);
                        text.applyFont(dataFont);
                        cell = rows.createCell(j);
                        cell.setCellValue(text);
                        cell.setCellStyle(dataStyle);
                    }
                }
                File file = new File(exportFile);
                fos = new FileOutputStream(file);
                workbook.write(fos);
            }
        } catch (IOException e) {
            log.error(e.getMessage(), e);
        } finally {
            try {
                workbook.close();
                if (null != fos) {
                    fos.close();
                }
            } catch (Exception e) {
                log.error(e.getMessage(), e);
            }
        }
    }

    public static void exportExcel(String exportFile, LinkedList<ExcelExportEntity> entityList, Collection<?> dataList) {
        exportExcel(exportFile, null, null, "Sheet1", ExcelType.XSSF, entityList, dataList);
    }

    public static void exportExcel(String exportFile, String title, LinkedList<ExcelExportEntity> entityList, Collection<?> dataList) {
        exportExcel(exportFile, title, null, "Sheet1", ExcelType.XSSF, entityList, dataList);
    }

    public static void exportExcel(String exportFile, String title, String
            sheetName, LinkedList<ExcelExportEntity> entityList, Collection<?> dataList) {
        exportExcel(exportFile, title, null, sheetName, ExcelType.XSSF, entityList, dataList);
    }

    public static void exportExcel(String exportFile, String title, String secondTitle, String sheetName, LinkedList<ExcelExportEntity> entityList, Collection<?> dataList) {
        exportExcel(exportFile, title, secondTitle, sheetName, ExcelType.XSSF, entityList, dataList);
    }

    /**
     * @param exportFile
     * @param title
     * @param secondTitle
     * @param sheetName
     * @param type:          ExcelType.XSSF,ExcelType.HSSF
     * @param entityList:    name 表头名, key 属性名
     * @param dataList:导出的数据
     */
    public static void exportExcel(String exportFile, String title, String secondTitle, String sheetName, ExcelType type, LinkedList<ExcelExportEntity> entityList, Collection<?> dataList) {
        Workbook workbook = null;
        FileOutputStream fos = null;
        try {
            ExportParams exportParams = new ExportParams(title, sheetName, type);
            exportParams.setAutoSize(true);
            exportParams.setStyle(ExcelStyleUtil.class);
            //exportParams.setHeaderColor(HSSFColor.HSSFColorPredefined.GREY_25_PERCENT.getIndex());
            //exportParams.setHeaderHeight(13);
            if (StringUtils.isNotBlank(secondTitle)) {
                exportParams.setSecondTitle(secondTitle);
            }
            workbook = ExcelExportUtil.exportExcel(exportParams, entityList, dataList);

            File file = new File(exportFile);
            fos = new FileOutputStream(file);
            workbook.write(fos);
        } catch (IOException e) {
            log.error(e.getMessage(), e);
        } finally {
            try {
                if (null != workbook) {
                    workbook.close();
                }
                if (null != fos) {
                    fos.close();
                }
            } catch (Exception e) {
                log.error(e.getMessage(), e);
            }
        }
    }

    /**
     * excel 导出
     *
     * @param list         数据列表
     * @param pojoClass    pojo类型
     * @param fileName     导出时的excel名称
     * @param response
     * @param exportParams 导出参数（标题、sheet名称、是否创建表头，表格类型）
     */
    private void defaultExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response, ExportParams exportParams) throws IOException {
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
        this.downLoadExcel(fileName, response, workbook);
    }

    /**
     * excel 导出
     *
     * @param list         数据列表
     * @param pojoClass    pojo类型
     * @param fileName     导出时的excel名称
     * @param exportParams 导出参数（标题、sheet名称、是否创建表头，表格类型）
     * @param response
     */
    public void exportExcel(List<?> list, Class<?> pojoClass, String fileName, ExportParams exportParams, HttpServletResponse response) throws IOException {
        this.defaultExport(list, pojoClass, fileName, response, exportParams);
    }

    /**
     * excel 导出
     *
     * @param list      数据列表
     * @param pojoClass pojo类型
     * @param fileName  导出时的excel名称
     * @param response
     */
    public void exportExcel(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response) throws IOException {
        this.exportExcel(list, null, "sheet1", pojoClass, fileName, response);
    }

    /**
     * excel 导出
     *
     * @param list      数据列表
     * @param title     表格内数据标题
     * @param sheetName sheet名称
     * @param pojoClass pojo类型
     * @param fileName  导出时的excel名称
     * @param response
     */
    public void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, HttpServletResponse response) throws IOException {
        if (list == null) {
            list = new ArrayList<>();
        }
        ExportParams exportParams = new ExportParams(title, sheetName, ExcelType.XSSF);
        if (fileName.contains(".xls")) {
            exportParams = new ExportParams(title, sheetName, ExcelType.HSSF);
        }
        exportParams.setAutoSize(Boolean.TRUE);
        exportParams.setStyle(ExcelStyleUtil.class);
        //exportParams.setHeight((short) 6);
        this.defaultExport(list, pojoClass, fileName, response, exportParams);
    }

    /**
     * 根据模板生成excel后导出
     *
     * @param templatePath 模板路径
     * @param map          数据集合
     * @param fileName     文件名
     * @param response
     * @throws IOException
     */
    public void exportExcel(TemplateExportParams templatePath, Map<String, Object> map, String fileName, HttpServletResponse response) throws IOException {
        Workbook workbook = ExcelExportUtil.exportExcel(templatePath, map);
        this.downLoadExcel(fileName, response, workbook);
    }

    /**
     * 根据模板生成excel后导出
     *
     * @param templateFile 模板路径
     * @param dataMap      数据集合
     * @throws IOException
     */
    public void exportExcel(String templateFile, String exportFile, Map<String, Object> dataMap) throws IOException {
        Workbook workbook = null;
        OutputStream outputStream = null;
        try {
            outputStream = new FileOutputStream(exportFile);
            workbook = ExcelExportUtil.exportExcel(new TemplateExportParams(templateFile), dataMap);
            workbook.write(outputStream);
            outputStream.flush();
        } catch (Exception e) {
            throw new IOException(e.getMessage());
        } finally {
            if (workbook != null) {
                workbook.close();
            }
            if (outputStream != null) {
                outputStream.close();
            }
        }
    }


    /**
     * excel下载
     *
     * @param fileName 下载时的文件名称
     * @param response
     * @param workbook excel数据
     */
    private void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) throws IOException {
        try {
            response.setCharacterEncoding("UTF-8");
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            //response.setHeader("responseType", "blob");
            response.setHeader("content-Type", "application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName + System.currentTimeMillis() + ".xlsx", "UTF-8"));
            //response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(new String(fileName.getBytes(StandardCharsets.UTF_8)) + System.currentTimeMillis() + ".xlsx", "UTF-8"));
            //response.setHeader("Content-Disposition", "attachment;filename*=UTF-8''" + URLEncoder.encode(fileName + System.currentTimeMillis() + ".xlsx", "UTF-8"));
            workbook.setForceFormulaRecalculation(true);
            workbook.write(response.getOutputStream());
        } catch (Exception e) {
            throw new IOException(e.getMessage());
        } finally {
            if (workbook != null) {
                workbook.close();
            }
        }
    }

    /**
     * excel 导入
     *
     * @param file      excel文件
     * @param pojoClass pojo类型
     * @param <T>
     * @return
     */
    public <T> List<T> importExcel(MultipartFile file, Class<T> pojoClass) throws IOException {
        return this.importExcel(file, 1, 1, pojoClass);
    }

    /**
     * excel 导入
     *
     * @param filePath   excel文件路径
     * @param titleRows  表格内数据标题行
     * @param headerRows 表头行
     * @param pojoClass  pojo类型
     * @param <T>
     * @return
     */
    public <T> List<T> importExcel(String filePath, Integer titleRows, Integer headerRows, Class<T> pojoClass) throws IOException {
        try {
            if (StringUtils.isBlank(filePath)) {
                return null;
            }
            ImportParams params = new ImportParams();
            params.setTitleRows(titleRows);
            params.setHeadRows(headerRows);
            //params.setNeedSave(true);
            //params.setSaveUrl("/excel/");
            return ExcelImportUtil.importExcel(new File(filePath), pojoClass, params);
        } catch (NoSuchElementException e) {
            throw new IOException("模板不能为空");
        } catch (Exception e) {
            throw new IOException(e.getMessage());
        }
    }


    /**
     * excel 导入
     *
     * @param file       上传的文件
     * @param titleRows  表格内数据标题行
     * @param headerRows 表头行
     * @param pojoClass  pojo类型
     * @param <T>
     * @return
     */
    public <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass) throws IOException {
        try {
            if (file == null) {
                return null;
            }
            return this.importExcel(file.getInputStream(), titleRows, headerRows, pojoClass);
        } catch (Exception e) {
            throw new IOException(e.getMessage());
        }
    }

    /**
     * excel 导入
     *
     * @param importFile 导入文件
     * @param targetFile 导入文件上传到服务器的目标文件
     * @param titleRows  表格内数据标题行
     * @param headerRows 表头行
     * @param pojoClass  pojo类型
     * @param <T>
     * @return
     */
    public <T> List<T> importExcel(String importFile, String targetFile, Integer titleRows, Integer headerRows, Class<T> pojoClass) throws IOException {
        try {
            if (StringUtils.isBlank(importFile)) {
                return null;
            }
            IOUtils.copy(new FileInputStream(importFile), new FileOutputStream(targetFile));
            return this.importExcel(targetFile, titleRows, headerRows, pojoClass);
        } catch (Exception e) {
            throw new IOException(e.getMessage());
        }
    }

    /**
     * excel 导入
     *
     * @param inputStream 文件输入流
     * @param titleRows   表格内数据标题行
     * @param headerRows  表头行
     * @param pojoClass   pojo类型
     * @param <T>
     * @return
     */
    public <T> List<T> importExcel(InputStream inputStream, Integer titleRows, Integer headerRows, Class<T> pojoClass) throws IOException {
        try {
            if (inputStream == null) {
                return null;
            }
            ImportParams params = new ImportParams();
            params.setTitleRows(titleRows);
            params.setHeadRows(headerRows);
            //params.setSaveUrl("/excel/");
            //params.setNeedSave(true);
            return ExcelImportUtil.importExcel(inputStream, pojoClass, params);
        } catch (NoSuchElementException e) {
            throw new IOException("excel文件不能为空");
        } catch (Exception e) {
            throw new IOException(e.getMessage());
        }
    }

    /**
     * excel 导入
     *
     * @param importFile 导入文件
     * @param titleRows  表格内数据标题行
     * @param headerRows 表头行
     * @param pojoClass  pojo类型
     * @param <T>
     * @return
     */
    public <T> ExcelImportResult<T> importExcelMore(String importFile, Integer titleRows, Integer headerRows, Class<T> pojoClass) throws IOException {
        FileInputStream fis = null;
        try {
            if (StringUtils.isBlank(importFile)) {
                return null;
            }
            fis = new FileInputStream(importFile);
            return this.importExcelMore(fis, titleRows, headerRows, pojoClass);
        } catch (Exception e) {
            throw new IOException(e.getMessage());
        } finally {
            try {
                if (fis != null) {
                    fis.close();
                }
            } catch (IOException e) {
                log.error("读取文件关闭文件流异常", e);
            }
        }
    }

    /**
     * excel 导入
     *
     * @param importFile 导入文件
     * @param targetFile 导入文件上传到服务器的目标文件
     * @param titleRows  表格内数据标题行
     * @param headerRows 表头行
     * @param pojoClass  pojo类型
     * @param <T>
     * @return
     */
    public <T> ExcelImportResult<T> importExcelMore(String importFile, String targetFile, Integer titleRows, Integer headerRows, Class<T> pojoClass) throws IOException {
        FileInputStream fis = null;
        try {
            if (StringUtils.isBlank(importFile)) {
                return null;
            }
            IOUtils.copy(new FileInputStream(importFile), new FileOutputStream(targetFile));
            fis = new FileInputStream(targetFile);
            return this.importExcelMore(fis, titleRows, headerRows, pojoClass);
        } catch (Exception e) {
            throw new IOException(e.getMessage());
        } finally {
            try {
                if (fis != null) {
                    fis.close();
                }
            } catch (IOException e) {
                log.error("读取文件关闭文件流异常", e);
            }
        }
    }

    /**
     * excel 导入
     *
     * @param inputStream 文件输入流
     * @param titleRows   表格内数据标题行
     * @param headerRows  表头行
     * @param pojoClass   pojo类型
     * @param <T>
     * @return
     */
    public <T> ExcelImportResult<T> importExcelMore(InputStream inputStream, Integer titleRows, Integer headerRows, Class<T> pojoClass) throws IOException {
        try {
            if (inputStream == null) {
                return null;
            }
            ImportParams params = new ImportParams();
            params.setTitleRows(titleRows);
            params.setHeadRows(headerRows);
            //params.setSaveUrl("/excel/");
            //params.setNeedSave(true);
            return ExcelImportUtil.importExcelMore(inputStream, pojoClass, params);
        } catch (NoSuchElementException e) {
            throw new IOException("excel文件不能为空");
        } catch (Exception e) {
            throw new IOException(e.getMessage());
        }
    }

    public static int rows(String importFile) throws Exception {
        Workbook workbook = null;
        ExcelReader reader = null;
        try {
            reader = cn.hutool.poi.excel.ExcelUtil.getReader(importFile);
            workbook = reader.getWorkbook();
            Sheet sheet = workbook.getSheetAt(0);
            int rows = sheet.getPhysicalNumberOfRows();
            return rows > 1 ? rows - 1 : rows;
        } catch (Exception e) {
            throw new Exception(e.getMessage());
        } finally {
            try {
                if (workbook != null) {
                    workbook.close();
                }
                if (reader != null) {
                    reader.close();
                }
            } catch (IOException e) {
                log.error("读取文件关闭文件流异常", e);
            }
        }
    }

    public static <T> List<T> importExcelByPage(String importFile, int headerRowIndex, Page page, Class<T> pojoClass) throws IOException {
        Workbook workbook = null;
        ExcelReader reader = null;
        List<T> dataList;
        try {
            if (StringUtils.isBlank(importFile)) {
                return null;
            }
            page = page == null ? new Page<>(1, 100) : page;
            page.setPageNumber(page.getPageNumber() < 1 ? 1 : page.getPageNumber());
            page.setPageSize(page.getPageSize() < 5 ? 100 : page.getPageSize());
            page.setStartRow(page.getStartRow() < 1 ? 1 : page.getStartRow());
            headerRowIndex = headerRowIndex > 0 ? headerRowIndex - 1 : 0;

            reader = cn.hutool.poi.excel.ExcelUtil.getReader(importFile);
            workbook = reader.getWorkbook();
            Sheet sheet = workbook.getSheetAt(0);
            int rows = sheet.getPhysicalNumberOfRows();
            rows = rows > 1 ? rows - 1 : rows;

            page.setTotal(rows);
            page.setEndRow(page.getEndRow() > rows ? rows : page.getEndRow());
            int startRowIndex = Integer.parseInt(String.valueOf(page.getStartRow()));
            startRowIndex = Math.max(startRowIndex, 1);
            startRowIndex = page.getPageNumber() == page.getLastPage() ? startRowIndex + 1 : startRowIndex;
            int endRowIndex = startRowIndex + page.getPageSize() - 1;
            endRowIndex = Math.min(endRowIndex, Integer.parseInt(String.valueOf(page.getTotal())));

            dataList = reader.read(headerRowIndex, startRowIndex, endRowIndex, pojoClass);
        } catch (Exception e) {
            throw new IOException(e.getMessage());
        } finally {
            try {
                if (workbook != null) {
                    workbook.close();
                }
                if (reader != null) {
                    reader.close();
                }
            } catch (IOException e) {
                log.error("读取文件关闭文件流异常", e);
            }
        }
        return dataList;
    }

    /**
     * <p>
     * 通过输入流创建workbook，单独调用记得关闭流（上面通过try()的方式会自动关闭流，因为他们实现了AutoCloseble）
     * </p>
     *
     * @param inputStream excel文件流
     * @return Workbook对象
     */
    public static Workbook getWorkbookByInputStream(InputStream inputStream) {
        try {
            return StreamingReader.builder()
                    .rowCacheSize(100)  //缓存到内存中的行数，默认是10
                    .bufferSize(4096)   //读取资源时，缓存到内存的字节大小，默认是1024
                    .open(inputStream); //打开资源，必须，可以是InputStream或者是File，注意：只能打开XLSX格式的文件
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * <p>
     * 通过文件创建workbook，单独调用记得关闭流
     * </p>
     *
     * @param file excel文件
     * @return Workbook对象
     */
    public static Workbook getWorkbookByFile(File file) {
        try (InputStream inputStream = new FileInputStream(file)) {
            return getWorkbookByInputStream(inputStream);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }


}

